package com.bj58.groupbuy.action.service.impl;

import java.util.ArrayList;
import java.util.List;

import com.bj58.groupbuy.action.db.DBConfig;
import com.bj58.groupbuy.action.db.UserPriviledgeDao;
import com.bj58.groupbuy.action.model.DBHostPwd;
import com.bj58.groupbuy.action.model.DBPriviledge;
import com.bj58.groupbuy.action.model.DBUser;
import com.bj58.groupbuy.action.service.DBService;
import com.bj58.groupbuy.service.impl.BaseService;
import com.bj58.groupbuy.wf.page.Paginate;

public class DBServiceImpl  extends BaseService<DBPriviledge> implements DBService {

	private static UserPriviledgeDao comDao = new UserPriviledgeDao();
	
	@Override
	public DBUser getUserByName(String username) throws Exception {
		String sql="select * from db_user where username = '"+username+"'";
		List<DBUser> user=comDao.getAllColsByVoLabel(sql, DBUser.class, DBConfig.db_68_bi_sys.getIntType());
		if(user==null || user.isEmpty())
			return null;
		else 
			return user.get(0);
	}

	@Override
	public boolean saveUser(DBUser user) throws Exception {
		int res=comDao.insertOneByVo(null, "db_user", DBUser.class, user, DBConfig.db_68_bi_sys.getIntType());
		return res==0?false:true;
	}

	@Override
	public boolean deleteUser(String username) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public DBHostPwd getHostPwd(String hostname) throws Exception {
		String sql="select * from db_host where hostname = '"+hostname+"'";
		List<DBHostPwd> hosts=comDao.getAllColsByVoLabel(sql, DBHostPwd.class, DBConfig.db_68_bi_sys.getIntType());
		if(hosts==null||hosts.isEmpty())
			return null;
		else
			return hosts.get(0);
	}

	@Override
	public List<DBPriviledge> getDBPriByUname(String username) throws Exception {
		String sql="select * from db_priviledge where username = '"+username+"'";
		return comDao.getAllColsByVoLabel(sql, DBPriviledge.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public DBPriviledge getDBPri(String username, String hostname,
			String database, String tablename) throws Exception {
		StringBuffer sql=new StringBuffer("select * from db_priviledge where username='");
		sql.append(username+"' and hostname='");
		sql.append(hostname+"' and dbname='");
		sql.append(database+"' and tablename='");
		sql.append(tablename+"'");
		List<DBPriviledge> priviledges=comDao.getAllColsByVoLabel(sql.toString(), DBPriviledge.class, DBConfig.db_68_bi_sys.getIntType());
		if(priviledges==null||priviledges.isEmpty())
			return null;
		else
			return priviledges.get(0);
	}

	@Override
	public boolean delDBPriByUname(String username) throws Exception {
		List<DBHostPwd> hostList=getAllHost();
		String revokesql = "revoke select,insert,update,delete,drop on *.* from '"+username+"'";
		String sql= "delete from db_priviledge where username='"+username;
		for(DBHostPwd host:hostList){
			String sqlcmd=sql+"' and hostname='"+host.getHostname()+"'";
			comDao.delDBPriv(host,sqlcmd,revokesql);
		}
		return true;
	}

	@Override
	public boolean delDBPriv(String username,String hostname,String dbname, String tableName,String priviledges) throws Exception {
		List<DBHostPwd> hosts=new ArrayList<DBHostPwd>();
		if(!"*".equals(hostname)){
			hosts.add(getHostPwd(hostname));
//			command = "grant "+ priviledges+" on "+dbname+"."+tablenames+" to '"+username+"'@'%' identified by "+password;
//			System.out.println(" the grant command is : "+command);
		}
		else
			hosts=getAllHost();
		return comDao.delDBPriv(username, hosts, dbname, tableName, priviledges);
	}
	
	@Override
	public boolean updateDBPri(String username, String password, String hostname, String dbname,String tablename, String priviledges) throws Exception {
		DBHostPwd host=getHostPwd(hostname);
		return comDao.updateDBPriv(username, password, host, dbname, tablename, priviledges);
	}
	
	@Override
	public List<DBHostPwd> getAllHost() throws Exception {
		String sql="select * from db_host";
		return comDao.getAllColsByVoLabel(sql, DBHostPwd.class, DBConfig.db_68_bi_sys.getIntType());
	}

	@Override
	public List<String> getDBNames(String hostname) {
		return comDao.getNames(hostname, null);
	}

	@Override
	public List<String> getTableNames(String hostname, String dbname) {
		return comDao.getNames(hostname, dbname);
	}

	@Override
	public boolean saveDBPriv(String username, String password, String hostname,String dbname, String tablenames,String priviledges) throws Exception {
		List<DBHostPwd> hosts=new ArrayList<DBHostPwd>();
		if(!"*".equals(hostname)){
			hosts.add(getHostPwd(hostname));
//			command = "grant "+ priviledges+" on "+dbname+"."+tablenames+" to '"+username+"'@'%' identified by "+password;
//			System.out.println(" the grant command is : "+command);
		}
		else
			hosts=getAllHost();
		return comDao.saveDBPriv(username, password, hosts, dbname, tablenames, priviledges);
	}

	@Override
	public Paginate<DBPriviledge> getDBPrivByPage(int pageIndex, int pageSize,
			String where) throws Exception {
		return getGenericDao().findForPage(DBPriviledge.class, where, "*", pageIndex, pageSize, "hostname desc");
	}

	

}
